import json
from django.shortcuts import render
from rest_framework.response import Response
from rest_framework.views import APIView
from rest_framework import status
from django.conf import settings
import os

import openpyxl
from openpyxl.cell import MergedCell

# Create your views here.
from .serializers import pfmea_table_infoSerializer, pfmea_table_dataSerializer
from rest_framework import viewsets
from .models import pfmea_table_info, pfmea_table_data


class pfmea_table_infoViewSet(viewsets.ModelViewSet):
    authentication_classes = []
    queryset = pfmea_table_info.objects.all()
    serializer_class = pfmea_table_infoSerializer


class pfmea_table_dataViewSet(viewsets.ModelViewSet):
    authentication_classes = []
    queryset = pfmea_table_data.objects.filter()
    serializer_class = pfmea_table_dataSerializer


class OpInfoView(APIView):
    authentication_classes = []

    def post(self, request, *args, **kwargs):
        base = request.data.get("base")
        base_zone = request.data.get("base_zone")
        partname = request.data.get("partname")
        OpInfo = list(pfmea_table_data.objects.filter(base=base, base_zone=base_zone, pfmea_info_id=partname).values_list("pfmea_op", flat=True).distinct().order_by("pfmea_op"))
        print(OpInfo)
        return Response(OpInfo)


# 用于获取pfmea数据的视图
class get_pfmea_dataView(APIView):
    authentication_classes = []

    def post(self, request, *args, **kwargs):
        base = request.data.get("base")
        base_zone = request.data.get("base_zone")
        pfmea_op = request.data.get("pfmea_op")
        partname = request.data.get("partName")
        pfmea_data = list(pfmea_table_data.objects.filter(base=base, base_zone=base_zone, pfmea_op=pfmea_op, pfmea_info_id=partname).values().order_by("pfmea_op"))
        print(type(pfmea_data))
        print(partname)
        return Response(pfmea_data)


# 用于插入pfmea数据的视图
class insert_pfmea_dataView(APIView):
    authentication_classes = []

    def post(self, request, *args, **kwargs):
        print("调用插入接口啦")
        # 创建一个空列表存储从用于插入的数据
        insert_data = []
        # 创建并保存模型实例
        n15_or_n12_pfmea_table_info = pfmea_table_info.objects.get(partName=request.data.get("pfmea_info"))
        # 附加信息
        base = request.data.get("base")
        base_zone = request.data.get("base_zone")
        pfmea_info = request.data.get("pfmea_info")
        pfmea_op = request.data.get("pfmea_op")
        # PFMEA表信息
        ProcessFunction = request.data.get("ProcessFunction")
        PotentialFailureMode = request.data.get("PotentialFailureMode")
        PotentialEffectsOfFailure = request.data.get("PotentialEffectsOfFailure")
        Severity = request.data.get("Severity")

        Class = request.data.get("Class")
        PotentialCausesOfFailure = request.data.get("PotentialCausesOfFailure")
        Occurence = request.data.get("Occurence")
        CurrentProcessControlsPrevention = request.data.get("CurrentProcessControlsPrevention")

        CurrentProcessControlsDetection = request.data.get("CurrentProcessControlsDetection")
        Detection = request.data.get("Detection")
        Rpn = request.data.get("Rpn")
        SeverZone = request.data.get("SeverZone")
        DetectZone = request.data.get("DetectZone")
        RiskPriorLevel = request.data.get("RiskPriorLevel")
        RecommendedActions = request.data.get("RecommendedActions")
        ResponsibilityAndTargetCompletionDate = request.data.get("ResponsibilityAndTargetCompletionDate")
        ActionTaken = request.data.get("ActionTaken")

        ActionTakenSeverity = request.data.get("ActionTakenSeverity")
        ActionTakenOccurence = request.data.get("ActionTakenOccurence")
        ActionTakenDetection = request.data.get("ActionTakenDetection")
        ActionTakenRpn = request.data.get("ActionTakenRpn")

        insert_data.append(
            pfmea_table_data(
                base=base,
                base_zone=base_zone,
                pfmea_op=pfmea_op,
                pfmea_info=n15_or_n12_pfmea_table_info,
                ProcessFunction=ProcessFunction,
                PotentialFailureMode=PotentialFailureMode,
                PotentialEffectsOfFailure=PotentialEffectsOfFailure,
                Severity=Severity,
                Class=Class,
                PotentialCausesOfFailure=PotentialCausesOfFailure,
                Occurence=Occurence,
                CurrentProcessControlsPrevention=CurrentProcessControlsPrevention,
                CurrentProcessControlsDetection=CurrentProcessControlsDetection,
                Detection=Detection,
                Rpn=Rpn,
                SeverZone=SeverZone,
                DetectZone=DetectZone,
                RiskPriorLevel=RiskPriorLevel,
                RecommendedActions=RecommendedActions,
                ResponsibilityAndTargetCompletionDate=ResponsibilityAndTargetCompletionDate,
                ActionTaken=ActionTaken,
                ActionTakenSeverity=ActionTakenSeverity,
                ActionTakenOccurence=ActionTakenOccurence,
                ActionTakenDetection=ActionTakenDetection,
                ActionTakenRpn=ActionTakenRpn,
            )
        )
        # 使用bulk_create批量导入
        pfmea_table_data.objects.bulk_create(insert_data)
        return Response({"result_info": "新增PFMEA成功！", "status": "success"})


# 用于编辑pfmea数据的视图
class edite_pfmea_dataView(APIView):
    authentication_classes = []

    def post(self, request, *args, **kwargs):
        print("调用编辑接口啦")
        # 创建一个空列表存储从用于更新编辑的数据
        edite_data = []
        # 创建并保存模型实例，编辑视图中的字段为pfmea_info_id而不是pfmea_info，注意与insert_pfmea_dataView中的区分
        n15_or_n12_pfmea_table_info = pfmea_table_info.objects.get(partName=request.data.get("pfmea_info_id"))
        id = request.data.get("id")
        # 附加信息
        base = request.data.get("base")
        base_zone = request.data.get("base_zone")
        pfmea_info = request.data.get("pfmea_info")
        pfmea_op = request.data.get("pfmea_op")
        # PFMEA表信息
        ProcessFunction = request.data.get("ProcessFunction")
        PotentialFailureMode = request.data.get("PotentialFailureMode")
        PotentialEffectsOfFailure = request.data.get("PotentialEffectsOfFailure")
        Severity = request.data.get("Severity")

        Class = request.data.get("Class")
        PotentialCausesOfFailure = request.data.get("PotentialCausesOfFailure")
        Occurence = request.data.get("Occurence")
        CurrentProcessControlsPrevention = request.data.get("CurrentProcessControlsPrevention")

        CurrentProcessControlsDetection = request.data.get("CurrentProcessControlsDetection")
        Detection = request.data.get("Detection")
        Rpn = request.data.get("Rpn")
        SeverZone = request.data.get("SeverZone")
        DetectZone = request.data.get("DetectZone")
        RiskPriorLevel = request.data.get("RiskPriorLevel")
        RecommendedActions = request.data.get("RecommendedActions")
        ResponsibilityAndTargetCompletionDate = request.data.get("ResponsibilityAndTargetCompletionDate")
        ActionTaken = request.data.get("ActionTaken")

        ActionTakenSeverity = request.data.get("ActionTakenSeverity")
        ActionTakenOccurence = request.data.get("ActionTakenOccurence")
        ActionTakenDetection = request.data.get("ActionTakenDetection")
        ActionTakenRpn = request.data.get("ActionTakenRpn")

        updata_data = pfmea_table_data.objects.get(id=int(id))
        # 更新编辑表单中所有字段
        updata_data.ProcessFunction = ProcessFunction
        updata_data.PotentialFailureMode = PotentialFailureMode
        updata_data.PotentialEffectsOfFailure = PotentialEffectsOfFailure
        updata_data.Severity = Severity
        updata_data.Class = Class
        updata_data.PotentialCausesOfFailure = PotentialCausesOfFailure
        updata_data.Occurence = Occurence
        updata_data.CurrentProcessControlsPrevention = CurrentProcessControlsPrevention
        updata_data.CurrentProcessControlsDetection = CurrentProcessControlsDetection
        updata_data.Detection = Detection
        updata_data.Rpn = Rpn
        updata_data.SeverZone = SeverZone
        updata_data.DetectZone = DetectZone
        updata_data.RiskPriorLevel = RiskPriorLevel
        updata_data.RecommendedActions = RecommendedActions
        updata_data.ResponsibilityAndTargetCompletionDate = ResponsibilityAndTargetCompletionDate
        updata_data.ActionTaken = ActionTaken
        updata_data.ActionTakenSeverity = ActionTakenSeverity
        updata_data.ActionTakenOccurence = ActionTakenOccurence
        updata_data.ActionTakenDetection = ActionTakenDetection
        updata_data.ActionTakenRpn = ActionTakenRpn

        edite_data.append(updata_data)

        # 使用bulk_update更新
        pfmea_table_data.objects.bulk_update(
            edite_data,
            [
                "ProcessFunction",
                "PotentialFailureMode",
                "PotentialEffectsOfFailure",
                "Severity",
                "Class",
                "PotentialCausesOfFailure",
                "Occurence",
                "CurrentProcessControlsPrevention",
                "CurrentProcessControlsDetection",
                "Detection",
                "Rpn",
                "SeverZone",
                "DetectZone",
                "RiskPriorLevel",
                "RecommendedActions",
                "ResponsibilityAndTargetCompletionDate",
                "ActionTaken",
                "ActionTakenSeverity",
                "ActionTakenOccurence",
                "ActionTakenDetection",
                "ActionTakenRpn",
            ],
        )

        return Response({"result_info": "编辑PFMEA成功！", "status": "success"})


class delete_pfmea_dataView(APIView):
    authentication_classes = []

    def post(self, request, *args, **kwargs):
        print("调用删除接口啦")
        delete_id = request.data.get("id")
        print(delete_id)
        delete_row = pfmea_table_data.objects.get(id=delete_id)
        delete_row.delete()

        return Response({"result_info": "删除PFMEA成功！", "status": "success"})


class data_manage_UploadView(APIView):
    authentication_classes = []

    def post(self, request, *args, **kwargs):
        files = request.FILES.getlist("file", None)
        date = request.POST.get("date")
        base = request.POST.get("base")
        base_zone = request.POST.get("base_zone")
        partName = request.POST.get("partName")
        print(date)
        print(base)
        print(base_zone)
        print(partName)
        if files is not None:
            for file in files:
                print(file)
                destination = os.path.join(settings.MEDIA_ROOT, file.name)
                with open(destination, "wb+") as destination_file:
                    for chunk in file.chunks():
                        destination_file.write(chunk)
                # 读取文件并写入数据库
                import_pfmea_excel_to_database(file, base, base_zone, partName)
                print("File uploaded successfully")
            return Response(data={"success": True, "message": "文件上传成功！"}, status=status.HTTP_200_OK)
        else:
            print("No file received")
            return Response(data={"success": False, "message": "没有接收到文件！"}, status=status.HTTP_400_BAD_REQUEST)


# 过滤worksheets列表
def filter_sheetnames(sheetnames):
    filtered_list = []
    for item in sheetnames:
        if item.find("OP", 0, 2) != -1:
            filtered_list.append(item)
    return filtered_list


# 实现读取合并单元格的数据
def merged_value(sheet, cell):
    # cell = sheet.cell(row, col)
    if isinstance(cell, MergedCell):
        for mergecell in sheet.merged_cells.ranges:
            if cell.coordinate in mergecell:
                cell = sheet.cell(row=mergecell.min_row, column=mergecell.min_col)
                break
    return cell.value


def import_pfmea_excel_to_database(excel_file, base: str, base_zone: str, partName: str):
    # 加载Excel文件
    workbook = openpyxl.load_workbook(excel_file, data_only=True)
    filtered_list = filter_sheetnames(workbook.sheetnames)
    # 创建一个空列表存储从excel读取到的数据
    import_data = []
    # 创建并保存模型实例
    n15_or_n12_pfmea_table_info = pfmea_table_info.objects.get(partName=partName)
    print(workbook.sheetnames)
    print(filtered_list)
    for worksheet_name in filtered_list:
        worksheet = workbook[worksheet_name]
        op_serial_number = worksheet.title.strip()  # 获取表头并去掉首位空格，避免存到数据库的字符含有空格导致无法匹配数据

        print("当前工作表表头：" + worksheet.title)
        print("当前工作表行数：" + str(worksheet.max_row))
        print("当前工作表列数：" + str(worksheet.max_column))

        # 遍历工作表中每一行
        for row in worksheet.iter_rows(min_row=10):  # 从第10行开始读取
            data1 = str(merged_value(worksheet, row[0]))  # 第1列数据 ProcessFunction
            data2 = str(row[1].value)  # 第2列数据 PotentialFailureMode
            data3 = str(row[2].value)  # 第3列数据 PotentialEffectsOfFailure
            data4 = str(row[3].value)  # 第4列数据 Severity
            data5 = str(row[4].value)  # 第5列数据 Class
            data6 = str(row[5].value)  # 第6列数据 PotentialCausesOfFailure
            data7 = str(row[6].value)  # 第7列数据 Occurence
            data8 = str(row[7].value)  # 第8列数据 CurrentProcessControlsPrevention

            data9 = str(row[8].value)  # 第9列数据 CurrentProcessControlsDetection
            data10 = str(row[9].value)  # 第10列数据 Detection
            data11 = str(row[10].value)  # 第11列数据 Rpn
            data12 = str(row[11].value)  # 第12列数据 SeverZone
            data13 = str(row[12].value)  # 第13列数据 DetectZone
            data14 = str(row[13].value)  # 第14列数据 RiskPriorLevel
            data15 = str(row[14].value)  # 第15列数据 RecommendedActions
            data16 = str(row[15].value)  # 第16列数据 ResponsibilityAndTargetCompletionDate

            data17 = str(row[16].value)  # 第17列数据 ActionTaken
            data18 = str(row[17].value)  # 第18列数据 ActionTakenSeverity
            data19 = str(row[18].value)  # 第19列数据 ActionTakenOccurence
            data20 = str(row[19].value)  # 第20列数据 ActionTakenDetection
            data21 = str(row[20].value)  # 第21列数据 ActionTakenRpn

            import_data.append(
                pfmea_table_data(
                    base=base,
                    base_zone=base_zone,
                    pfmea_op=op_serial_number,
                    ProcessFunction=data1,
                    PotentialFailureMode=data2,
                    PotentialEffectsOfFailure=data3,
                    Severity=data4,
                    Class=data5,
                    PotentialCausesOfFailure=data6,
                    Occurence=data7,
                    CurrentProcessControlsPrevention=data8,
                    CurrentProcessControlsDetection=data9,
                    Detection=data10,
                    Rpn=data11,
                    SeverZone=data12,
                    DetectZone=data13,
                    RiskPriorLevel=data14,
                    RecommendedActions=data15,
                    ResponsibilityAndTargetCompletionDate=data16,
                    ActionTaken=data17,
                    ActionTakenSeverity=data18,
                    ActionTakenOccurence=data19,
                    ActionTakenDetection=data20,
                    ActionTakenRpn=data21,
                    pfmea_info=n15_or_n12_pfmea_table_info,
                )
            )
    # 使用bulk_create批量导入
    pfmea_table_data.objects.bulk_create(import_data)
